package Mqsql01;

import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 * @Author 汤舒青
 * @Version 1.0
 */
//设计一个DbUtil类，处理相关数据库的操作，
//        包括获得链接，关闭相关资源的方法
//        最好用Static方法
//统计学生总数。并输出。
public class Dbutil {
    static Connection con = null;
    static ResultSet ret = null;
    static Statement st = null;

    public static void getinterlinkage() {
        //获取Driver实现类对象
        try {
            Driver driver = new com.mysql.cj.jdbc.Driver();
            //提供连接的数据库
            String url = "jdbc:mysql://localhost:3306/db400?useSSL=false&characterEncoding=UTF-8&serverTimezone=GMT%2b8";
            //提供用户名和密码
            Properties properties = new Properties();
            properties.setProperty("user", "root");
            properties.setProperty("password", "147258369");
            con = driver.connect(url, properties);
            System.out.println("连接数据库成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //统计总人数
    public static int count() {
        //统计学生格式
        int count = 0;
        try {
            st = con.createStatement();
            ResultSet ret = st.executeQuery("SELECT COUNT(*)  FROM s_student");
            //System.out.println(con);

            if (ret.next()) {
                count = ret.getInt("count(*)");
                System.out.println("学生总人数为" + count);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;

    }

    //  增加一个课程，相关信息输入
    public static int addsub(String sql) {
        int k = 0;
        try {
            st = con.createStatement();
            k = st.executeUpdate(sql);
            //System.out.println("增加课程成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return k;
    }
//修改一个课程信息，通过id（编号）
    //在控制台输入一个学生的全部信息，保存到数据库，
    //注意日期的处理(PreparedStatement)


    public static void close(Statement st, ResultSet ret, Connection con) {
        //关闭资源
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (ret != null) {
                //关闭资源
                try {
                    ret.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                if (con != null) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        getinterlinkage();
        count();
        System.out.println("请输入您要增加的课程名");
        String subname = input.next();
        System.out.println("请输入您要增加的课程的时长");
        int hour = input.nextInt();
        System.out.println("请输入您要增加的课程的年级编号");
        int id = input.nextInt();
        String sql = "INSERT INTO s_subject(subjectname,classhour,gradeid) VALUES" + "(" + "'" + subname + "'" + "," + "'" + hour + "'" + "," + "'" + id + "'" + ")";
        addsub(sql);
        System.out.println("课程添加成功");

        System.out.println("请输入您要修改的课程id");
        int id1 = input.nextInt();
        System.out.println("请输入您要修改的信息 (1:subjectname/2:classhour,3:gradeid)");
        int chioce = input.nextInt();
        switch (chioce) {
            case 1:
                System.out.println("请输入需要修改的新课程名称");
                String newname = input.next();
                String sql2 = "update s_subject set subjectname = '" + newname + "',classhour = '" + "',gradeid ='" + "'where id='" + id1 + "'";
                System.out.println("修改成功");
                addsub(sql2);
                break;
            case 2:
                System.out.println("请输入要修改的课时");
                int classhour = input.nextInt();
                String sql3 = "update s_subject set subjectname = '" + "',classhour = '" + classhour + "',gradeid ='" + "'where id='" + id1 + "'";
                System.out.println("修改成功");
                addsub(sql3);
                break;
            case 3:
                System.out.println("请输入要修改的年级号");
                int grade = input.nextInt();
                String sql4 = "update s_subject set subjectname = '" + "',classhour = '" + "',gradeid ='" + grade + "'where id='" + id1 + "'";
                System.out.println("修改成功");
                addsub(sql4);
                break;
            default:
                break;
        }
        System.out.println("请输入学生姓名");
        String name = input.next();
        System.out.println("请输入学生性别");
        String sex = input.next();
        System.out.println("请输入学生电话号码");
        String number = input.next();
        System.out.println("请输入学生qq");
        String qq = input.next();
        System.out.println("请输入学生微信");
        String wx = input.next();
        System.out.println("请输入学生年级号");
        int gid = input.nextInt();
        System.out.println("请输入学生入学时间");
        String date = input.next();
        System.out.println("请输入学生来自哪个城市");
        String city = input.next();
        System.out.println("请输入学生生日");
        String bith = input.next();
        System.out.println("请输入学生家庭地址");
        String add = input.next();
        System.out.println("请输入学生密码");
        String pass = input.next();
        System.out.println("请输入学生身份证");
        String cardc = input.next();
        try {
            PreparedStatement pr = con.prepareStatement("insert into s_student (sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic)values (?,?,?,?,?,?,?,?,?,?,?,?)");
            pr.setString(1, name);
            pr.setString(2, sex);
            pr.setString(3, number);
            pr.setString(4, qq);
            pr.setString(5, wx);
            pr.setInt(6, gid);
            pr.setDate(7, Date.valueOf(date));
            pr.setString(8, city);
            pr.setDate(9, Date.valueOf(bith));
            pr.setString(10, add);
            pr.setString(11, pass);
            pr.setString(12, cardc);
            int i = pr.executeUpdate();

            if (i > 0) {
                System.out.println("添加成功");
            } else {
                System.out.println("添加失败");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }
        close(st, ret, con);


    }

}
